﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;

namespace Follow.DataAccess
{
    public class RelatorioPropostaDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public RelatorioPropostaDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<RelatorioProposta> findAll(int idfuncionario, bool gestor, string Mes, string Ano)
        {
            IList<RelatorioProposta> relatorioProposta = new List<RelatorioProposta>();
            try
            {
                conn.Open();
                String sqlCommand = @" SELECT [nomeFuncionario]
                                          ,[statusDescricao]
                                          ,month([dataproposta]) mes
	                                      ,year([dataproposta]) ano
	                                      ,count(*) qtde
                                      FROM [vwFollowUp]
                                      where month([dataproposta]) = " + Mes + @" and year([dataproposta]) = " + Ano;


                if (!gestor)
                {
                    sqlCommand += @" and funcionario_idfuncionario =" + idfuncionario.ToString() + @"
                                      group by  [nomeFuncionario]
                                          ,[statusDescricao]
                                          ,month([dataproposta]) 
	                                      ,year([dataproposta]) order by [nomeFuncionario], [statusDescricao]"; ;
                }
                else
                {
                    sqlCommand += @" and funcionario_idfuncionario in (SELECT funcionario_idfuncionario
                                                                        FROM [equipefuncionario]
                                                                        where equipe_idequipe = (SELECT equipe_idequipe
                                                                        FROM [equipefuncionario]
                                                                        where funcionario_idfuncionario = " + idfuncionario.ToString() + @"

                                      )) group by  [nomeFuncionario]
                                          ,[statusDescricao]
                                          ,month([dataproposta]) 
	                                      ,year([dataproposta]) order by [nomeFuncionario], [statusDescricao]";

                }
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    RelatorioProposta rp = new RelatorioProposta();

                    rp.NomeFuncionario = reader.GetString(0);
                    rp.statusDescricao = reader.GetString(1);
                    rp.Mes = reader.GetInt32(2);
                    rp.Ano = reader.GetInt32(3);
                    rp.QuantidadeProposta = reader.GetInt32(4);

                    relatorioProposta.Add(rp);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return relatorioProposta;

        }
    }
}